﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace syc.Sale
{
    public partial class 产品装车单 : System.Web.UI.Page
    {
        protected NPOI.HSSF.UserModel.HSSFWorkbook hssfworkbook;
        protected string CID;
        protected string DID;
        protected string DT;
        protected string 车数;
        protected string 总捆数;
        protected string 总支数;
        protected string 总吨位;
        protected string 总米数;

        protected string 品名;
        protected string 订货单位;
        protected string 规格;
        protected string 捆数;
        protected string 支数;
        protected string 吨位;
        protected string 米数;
        protected string 车号;

        protected void Page_Load(object sender, EventArgs e)
        {
            CID = Request.QueryString["CID"];
            DID = Request.QueryString["DID"];
            DT = Request.QueryString["DT"];
            if (!IsPostBack)
            {
                Query();
            }
        }
        public void Query()
        {
            SqlConnection Conn = new SqlConnection();
            Conn.ConnectionString = ConfigurationManager.ConnectionStrings["SCMConnectionString1"].ConnectionString;
            Conn.Open();
            SqlCommand Cmd = new SqlCommand();
            Cmd.Connection = Conn;
            string sql1 = @"         
            declare @车数 int
            declare @总捆数 int
            declare @总支数 int
            declare @总吨位 float
            declare @总米数 float
Select @车数=count(*),@总捆数=isnull(sum(整车捆数),0),@总吨位=isnull(sum(整车吨位),0)/1000,@总支数=isnull(sum(整车支数),0),@总米数=isnull(sum(整车米数),0)
from [产品出库清单] where 是否合格=1 and 发运时间='@date' and 产品类别ID=@CID and 订货单位ID=@DID

select @车数,@总捆数,@总支数,@总吨位,@总米数
";
            sql1 = sql1.Replace("@date", DateTime.Parse(DT).ToShortDateString());
            sql1 = sql1.Replace("@CID", CID);
            sql1 = sql1.Replace("@DID", DID);
            Cmd.CommandText = sql1;

            SqlDataReader Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                车数 = Reader.GetInt32(0).ToString();
                总捆数 = Reader.GetInt32(1).ToString();
                总支数 = Reader.GetInt32(2).ToString();
                总吨位 = Reader.GetDouble(3).ToString("0.000");
                总米数 = Reader.GetDouble(4).ToString();
            }
            Reader.Close();
            Reader.Dispose();
            Cmd.Dispose();
            Conn.Close();
            Conn.Dispose();
        }
        protected void InitializeWorkbook()
        {

            FileStream file = new FileStream(HttpContext.Current.Server.MapPath("/Sale/模板/产品装车单.xls"), FileMode.Open, FileAccess.Read);

            hssfworkbook = new HSSFWorkbook(file);

            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "产品装车单";
            hssfworkbook.DocumentSummaryInformation = dsi;

            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "报表";
            si.Title = "产品装车单";
            hssfworkbook.SummaryInformation = si;
        }
        protected void WriteToFile()
        {           
            Response.ContentType = "application/ms-excel";
            Response.AddHeader("content-disposition", "attachment; filename=\"" + DateTime.Now.ToShortDateString() + ".xls\"");
            hssfworkbook.Write(Response.OutputStream);
        }
        protected void Button_To_Click(object sender, EventArgs e)
        {
            Query();
            InitializeWorkbook();
            HSSFSheet sheet1 = hssfworkbook.GetSheet("产品装车单");
            sheet1.GetRow(2).GetCell(0).SetCellValue("收 货 单 位:丰源公司 -- " + this.DPDH.SelectedItem.Text);
            string text = "关于:我公司于" + DT + "发往贵公司" + 车数 + "车，具体明细见下表。望收到请核对，如有问题请及时给予回复，谢谢合作!";
            sheet1.GetRow(6).GetCell(0).SetCellValue(text);

            SqlConnection Conn = new SqlConnection();
            Conn.ConnectionString = ConfigurationManager.ConnectionStrings["SCMConnectionString1"].ConnectionString;
            Conn.Open();
            SqlCommand Cmd = new SqlCommand();
            Cmd.Connection = Conn;
            string sql1 = @"                  
Select 
case when 产品类别ID=1 then '油管' when 产品类别ID=2 then '套管' when 产品类别ID=3 then '油管' when 产品类别ID=4 then '套管'  end as 品名,
case when 产品类别ID=1 then '73.02x5.51 J55' when 产品类别ID=2 then '139.7x7.72 J55' when 产品类别ID=3 then '73.02x5.51 N80Q' when 产品类别ID=4 then '139.7x7.72 N80Q'  end as 规格,
发往单位,
整车捆数,isnull(整车吨位,0)/1000,整车支数,isnull(整车米数,0),车号
from [产品出库清单] where 是否合格=1 and 发运时间='@date' and 产品类别ID=@CID and 订货单位ID=@DID order by ID desc
";
            sql1 = sql1.Replace("@date", DateTime.Parse(DT).ToShortDateString());
            sql1 = sql1.Replace("@CID", CID);
            sql1 = sql1.Replace("@DID", DID);
            Cmd.CommandText = sql1;
            SqlDataReader Reader = Cmd.ExecuteReader();
            int i = 0;
            while (Reader.Read())
            {
                i = i + 1;
                品名 = Reader.GetString(0).ToString();
                规格 = Reader.GetString(1).ToString();
                订货单位 = Reader.GetString(2).ToString();
                捆数 = Reader.GetSqlValue(3).ToString();
                吨位 = Reader.GetDecimal(4).ToString("0.000");
                支数 = Reader.GetSqlValue(5).ToString();
                米数 = Reader.GetSqlValue(6).ToString();
                车号 = Reader.GetString(7).ToString();
                sheet1.GetRow(7+i).GetCell(0).SetCellValue(i);             
                sheet1.GetRow(7 + i).GetCell(1).SetCellValue(Request.QueryString["DT"]);
                sheet1.GetRow(7 + i).GetCell(3).SetCellValue(this.DPDH.SelectedItem.Text);
                sheet1.GetRow(7 + i).GetCell(4).SetCellValue(品名);
                sheet1.GetRow(7 + i).GetCell(5).SetCellValue(规格);
                sheet1.GetRow(7 + i).GetCell(6).SetCellValue("见质保单");
                sheet1.GetRow(7 + i).GetCell(7).SetCellValue(捆数);
                sheet1.GetRow(7 + i).GetCell(8).SetCellValue(支数);
                sheet1.GetRow(7 + i).GetCell(9).SetCellValue(吨位);
                sheet1.GetRow(7 + i).GetCell(10).SetCellValue(米数);
                sheet1.GetRow(7 + i).GetCell(11).SetCellValue(车号.Trim());
            }
            Reader.Close();
            Reader.Dispose();
            Cmd.Dispose();
            Conn.Close();
            Conn.Dispose();
            sheet1.GetRow(25).GetCell(7).SetCellValue(总捆数);
            sheet1.GetRow(25).GetCell(8).SetCellValue(总支数);
            sheet1.GetRow(25).GetCell(9).SetCellValue(总吨位);
            sheet1.GetRow(25).GetCell(10).SetCellValue(总米数);
            string op = (string)this.Session["OperatorName"];
            if (op == null)
            {
                op = "";
            }
            sheet1.GetRow(26).GetCell(5).SetCellValue("经办人:"+op);
            WriteToFile();
            Response.End();
        }
       
    }
}
